use master
go

-- drop existing database if it exists.
if exists (select * from sysdatabases where name='DiagnosticsManagement')
		drop database DiagnosticsManagement
go

-- determine the installation folder.
declare @device_directory nvarchar(520)
select @device_directory = substring(filename, 1, charindex(N'master.mdf', lower(filename)) - 1)
from master.dbo.sysaltfiles 
where dbid = 1 and fileid = 1

-- create the DiagnosticsManagement database within the appropriate folder.
execute (N'create database DiagnosticsManagement
  on primary (name = N''DiagnosticsManagement'', filename = N'''  + @device_directory + N'DiagnosticsManagement.mdf'')
  log on (name = N''DiagnosticsManagement_log'',  filename = N''' + @device_directory + N'DiagnosticsManagement.ldf'')')
go

if exists (select * from syslogins where loginname = N'DiagnosticsUser')
begin
    drop login DiagnosticsUser
end

-- create login 'DiagnosticsUser'
create login [DiagnosticsUser]
with password = 'DiagnosticsUser',
default_database = DiagnosticsManagement

use DiagnosticsManagement
go

-- create role 'DiagnosticsRole'
create role [DiagnosticsRole] authorization [dbo]
go

-- create user 'DiagnosticsUser'
create user [DiagnosticsUser] for login [DiagnosticsUser]
	with default_schema = [dbo]
go

-- assign roles
execute sp_addrolemember
	@rolename		= N'DiagnosticsRole'
	, @membername	= N'DiagnosticsUser'
go

-- create Exception table
create table Exception
(
	[ExceptionId]					    int					not null		identity(1, 1)	primary key
	, [WorkItemId]						int					null
	, [PublicationId]					uniqueidentifier	not null
	, [AssemblyId]                      int					not null
	, [TargetSiteId]					int					null
	, [ThreadId]                        int					not null
	, [AppDomainId]                     int					not null
	, [MachineId]                       int					not null
	, [ProcessId]						int					not null
	, [ExceptionType]					nvarchar(255)		not null	
	, [HelpLink]						nvarchar(512)		null
	, [HResult]						    int					not null
	, [InnerExceptionId]				int					null
	, [IsTransient]					    bit					null
	, [Message]						    nvarchar(512)		not null
	, [Source]						    nvarchar(512)		null
	, [StackTrace]					    nvarchar(max)		null	
	, [SerializedExceptionXml]		    xml					null
	, [SerializedIIdentityXml]		    xml					null
	, [IIdentityName]					nvarchar(255)		not null
	, [IIdentityIsAuthenticated]		bit					not null
	, [IIdentityAuthenticationType]	    nvarchar(100)		not null
	, [Created]						    datetime			not null
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[Exception] add constraint [DF_Exception_Created] default (getdate()) for [Created]
go

-- create LogEntry table
create table LogEntry
(
	[LogEntryId]						int					not null		identity(1, 1) primary key
	, [WorkItemId]						int					null
	, [PublicationId]					uniqueidentifier	not null
	, [AssemblyId]                      int					not null
	, [ThreadId]                        int					not null
	, [AppDomainId]                     int					not null
	, [MachineId]                       int					not null
	, [ProcessId]						int					not null
	, [LogEntry]						nvarchar(max)		not null
	, [Priority]                        int                 not null
	, [Type]                            int                 not null
	, [SerializedIIdentityXml]		    xml                 not null
	, [IIdentityName]				    nvarchar(255)       not null
	, [IIdentityIsAuthenticated]	    bit                 not null
	, [IIdentityAuthenticationType]	    nvarchar(100)       not null
	, [Created]							datetime			not null
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[LogEntry] add constraint [DF_LogEntry_Created] default (getdate()) for [Created]
go

-- create TeamFoundationServer table
create table TeamFoundationServer
(
	[TeamFoundationServerId]			int					not null		identity(1, 1) primary key
	, [Uri]								nvarchar(255)		not null
	, [Created]							datetime			not null
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[TeamFoundationServer] add constraint [DF_TeamFoundationServer_Created] default (getdate()) for [Created]

-- create WorkItem table
create table WorkItem
(
	[WorkItemId]						int					not null		identity(1, 1) primary key
	, [TeamFoundationServerId]			int					not null
	, [Created]							datetime			not null
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[WorkItem] add constraint [DF_WorkItem_Created] default (getdate()) for [Created]
go

-- create foreign key constraint 'FK_WorkItem_TeamFoundationServerId'
alter table [dbo].[WorkItem] add constraint [FK_WorkItem_TeamFoundationServerId] foreign key (TeamFoundationServerId) references [TeamFoundationServer](TeamFoundationServerId)
go

-- create foreign key constraint 'FK_Exception_WorkItemId'
alter table [dbo].[Exception] add constraint [FK_Exception_WorkItemId] foreign key (WorkItemId) references [WorkItem](WorkItemId)
go

-- create foreign key constraint 'FK_LogEntry_WorkItemId'
alter table [dbo].[LogEntry] add constraint [FK_LogEntry_WorkItemId] foreign key (WorkItemId) references [WorkItem](WorkItemId)
go

-- create Machine table
create table Machine
(
    [MachineId]                         int					not null        identity(1, 1) primary key
    , [MachineName]					    nvarchar(100)		not null
	, [MachineIPAddress]				nvarchar(100)		not null
	, [OSVersion]                       nvarchar(100)		not null
	, [ProcessorCount]                  int					not null
	, [InstalledMemory]                 bigint				not null
	, [InstalledMemorySpeed]            int					not null
	, [Created]                         datetime			not null                  
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[Machine] add constraint [DF_Machine_Created] default (getdate()) for [Created]
go

-- create foreign key constraint 'FK_Exception_Machine'
alter table [dbo].[Exception] add constraint [FK_Exception_Machine] foreign key (MachineId) references [Machine](MachineId)
go

-- create foreign key constraint 'FK_LogEntry_Machine'
alter table [dbo].[LogEntry] add constraint [FK_LogEntry_Machine] foreign key (MachineId) references [Machine](MachineId)
go

-- create MachineProcesor table
create table MachineProcessor
(
    [MachineId]                         int					not null		
    , [ProcessorId]                     int					not null		
)

-- create Processor table
create table Processor
(
    [ProcessorId]                       int					not null        identity(1, 1) primary key
    , [DeviceId]                        nvarchar(20)		not null
    , [Architecture]                    int					not null
    , [Name]                            nvarchar(100)		not null
    , [Description]                     nvarchar(100)		not null
    , [Manufacturer]					nvarchar(100)		not null
    -- Win32_Processor class NumberOfCores property does not exist on versions of the Windows operating system prior to Windows Vista and Windows 2008 (codename Longhorn),
    -- as such this column will be null for exceptions and log entries published on machines running earlier versions of the Windows operating system.
    , [Cores]							int					null
    , [Created]                         datetime			not null
)

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[Processor] add constraint [DF_Processor_Created] default (getdate()) for [Created]
go

-- create foreign key constraint 'FK_MachineProcessor_Machine'
alter table [dbo].[MachineProcessor] add constraint [FK_MachineProcessor_Machine] foreign key (MachineId) references [Machine](MachineId)
go

-- create foreign key constraint 'FK_MachineProcessor_Processor'
alter table [dbo].[MachineProcessor] add constraint [FK_MachineProcessor_Processor] foreign key (ProcessorId) references [Processor](ProcessorId)
go

-- create MachineGraphicsProcessor table
create table MachineGraphicsProcessor
(
    [MachineId]                         int					not null
    , [GraphicsProcessorId]             int					not null
)

go

-- create GraphicsProcessor table
create table GraphicsProcessor
(
    [GraphicsProcessorId]               int					not null        identity(1, 1) primary key
    , [DeviceId]                        nvarchar(20)		not null
    , [Name]                            nvarchar(100)		not null
    , [Description]                     nvarchar(100)		not null
    , [InstalledMemory]                 bigint				not null
    , [DriverVersion]                   nvarchar(100)		null
    , [Created]                         datetime			not null
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[GraphicsProcessor] add constraint [DF_GraphicsProcessor_Created] default (getdate()) for [Created]
go

-- create foreign key constraint 'FK_MachineGraphicsProcessor_Machine'
alter table [dbo].[MachineGraphicsProcessor] add constraint [FK_MachineGraphicsProcessor_Machine] foreign key (MachineId) references [Machine](MachineId)
go

-- create foreign key constraint 'FK_MachineGraphicsProcessor_GraphicsProcessor'
alter table [dbo].[MachineGraphicsProcessor] add constraint [FK_MachineGraphicsProcessor_GraphicsProcessor] foreign key (GraphicsProcessorId) references [GraphicsProcessor](GraphicsProcessorId)
go

-- create TargetSite table
create table TargetSite
(
	[TargetSiteId]					    int					not null		identity(1, 1)	primary key
	, [IsAbstract]					    bit					not null
	, [IsAssembly]					    bit					not null
	, [IsConstructor]					bit					not null
	, [IsFamily]						bit					not null
	, [IsFamilyAndAssembly]			    bit					not null
	, [IsFamilyOrAssembly]			    bit					not null
	, [IsFinal]						    bit					not null
	, [IsHideBySignature]				bit					not null
	, [IsPrivate]						bit					not null
	, [IsPublic]						bit					not null
	, [IsSpecialName]					bit					not null
	, [IsStatic]						bit					not null
	, [IsVirtual]						bit					not null
	, [Created]                         datetime			not null
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[TargetSite] add constraint [DF_TargetSite_Created] default (getdate()) for [Created]
go

-- create foreign key constraint 'FK_Exception_TargetSite'
alter table [dbo].[Exception] add constraint [FK_Exception_TargetSite] foreign key (TargetSiteId) references [TargetSite](TargetSiteId)
go

-- create Assembly table
create table Assembly
(
    [AssemblyId]                        int					not null        identity(1, 1) primary key
    , [CodeBase]                        nvarchar(1024)		not null
    , [EntryPoint]                      nvarchar(1024)		null
    , [FullName]                        nvarchar(512)		not null
    , [GlobalAssemblyCache]             bit					not null
    , [HostContext]                     bigint				not null
    , [ImageRuntimeVersion]             nvarchar(100)		not null
    , [Location]                        nvarchar(512)		not null
    , [ManifestModule]                  nvarchar(1024)		not null
    , [DebugBuild]                      bit					not null
    , [Created]                         datetime			not null
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[Assembly] add constraint [DF_Assembly_Created] default (getdate()) for [Created]
go

-- create foreign key constraint 'FK_Exception_Assembly'
alter table [dbo].[Exception] add constraint [FK_Exception_Assembly] foreign key (AssemblyId) references [Assembly](AssemblyId)
go

-- create foreign key constraint 'FK_LogEntry_Assembly'
alter table [dbo].[LogEntry] add constraint [FK_LogEntry_Assembly] foreign key (AssemblyId) references [Assembly](AssemblyId)
go

-- create Thread table
create table Thread
(
    [ThreadId]                          int					not null        identity(1, 1) primary key
    , [IsAlive]                         bit					not null
    , [IsBackground]                    bit					not null
    , [IsThreadPoolThread]              bit					not null
    , [ManagedThreadId]                 int					not null
    , [Name]                            nvarchar(512)
    , [Priority]                        nvarchar(100)		not null
    , [ThreadState]                     nvarchar(100)		not null
    , [CurrentCulture]					nvarchar(100)		not null
    , [CurrentUICulture]				nvarchar(100)		not null
    , [Created]                         datetime			not null
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[Thread] add constraint [DF_Thread_Created] default (getdate()) for [Created]
go

-- create foreign key constraint 'FK_Exception_Thread'
alter table [dbo].[Exception] add constraint [FK_Exception_Thread] foreign key (ThreadId) references [Thread](ThreadId)
go

-- create foreign key constraint 'FK_LogEntry_Thread'
alter table [dbo].[LogEntry] add constraint [FK_LogEntry_Thread] foreign key (ThreadId) references [Thread](ThreadId)
go

-- create AppDomain table
create table AppDomain
(
    [AppDomainId]                       int					not null        identity(1, 1) primary key
    , [BaseDirectory]                   nvarchar(1024)		not null
    , [DomainManager]                   nvarchar(100)		null
    , [FriendlyName]                    nvarchar(1024)		not null
    , [Id]                              int					not null
    , [Created]                         datetime			not null 
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[AppDomain] add constraint [DF_AppDomain_Created] default (getdate()) for [Created]
go

-- create foreign key constraint 'FK_Exception_AppDomain'
alter table [dbo].[Exception] add constraint [FK_Exception_AppDomain] foreign key (AppDomainId) references [AppDomain](AppDomainId)
go

-- create foreign key constraint 'FK_LogEntry_AppDomain'
alter table [dbo].[LogEntry] add constraint [FK_LogEntry_AppDomain] foreign key (AppDomainId) references [AppDomain](AppDomainId)
go

-- create Process table
create table Process
(
	[ProcessId]							int					not null		identity(1, 1) primary key
	, [BasePriority]					int					not null
	, [ExitCode]						int					null
	, [ExitTime]						datetime			null
	, [HasExited]						bit					not null
	, [Id]								int					not null
	, [NonpagedSystemMemorySize64]		bigint				not null
	, [PagedMemorySize64]				bigint				not null
	, [PagedSystemMemorySize64]			bigint				not null
	, [PeakPagedMemorySize64]			bigint				not null
	, [PeakVirtualMemorySize64]			bigint				not null
	, [PeakWorkingSet64]				bigint				not null
	, [PriorityBoostEnabled]			bit					not null
	, [PrivateMemorySize64]				bigint				not null
	, [PrivilegedProcessorTime]			nvarchar(40)			not null
	, [ProcessName]						nvarchar(100)			not null
	, [ProcessorAffinity]				int					not null
	, [Responding]						bit					not null
	, [SessionId]						int					not null
	, [StartTime]						datetime			not null
	, [Threads]							int					not null
	, [TotalProcessorTime]				nvarchar(40)			not null
	, [UserProcessorTime]				nvarchar(40)			not null
	, [VirtualMemorySize64]				bigint				not null
	, [WorkingSet64]					bigint				not null
    , [Created]                         datetime			not null 
)

go

-- create constraint to set the 'Created' datetime column.
alter table [dbo].[Process] add constraint [DF_Process_Created] default (getdate()) for [Created]
go

-- create foreign key constraint 'FK_Exception_Process'
alter table [dbo].[Exception] add constraint [FK_Exception_Process] foreign key (ProcessId) references [Process](ProcessId)
go

-- create foreign key constraint 'FK_LogEntry_Process'
alter table [dbo].[LogEntry] add constraint [FK_LogEntry_Process] foreign key (ProcessId) references [Process](ProcessId)
go

-- create stored procedure 'usp_LogEntryInsert'
create procedure [dbo].[usp_LogEntryInsert]
    @LogEntryId                         int                 output
    , @PublicationId					uniqueidentifier
    , @AssemblyId                       int
	, @ThreadId                         int
	, @AppDomainId                      int
	, @MachineId                        int
	, @ProcessId						int
    , @LogEntry                         nvarchar(max)
    , @Priority                         int
	, @Type                             int
	, @SerializedIIdentityXml 		    xml
	, @IIdentityName 				    nvarchar(255)
	, @IIdentityIsAuthenticated 	    bit
	, @IIdentityAuthenticationType 	    nvarchar(100)
as

set nocount on

begin try

    begin transaction
    
    insert into [dbo].[LogEntry]
    (
		PublicationId
		, AssemblyId
		, ThreadId
		, AppDomainId
		, MachineId
		, ProcessId
        , LogEntry
        , Priority
        , Type
        , SerializedIIdentityXml	
        , IIdentityName			
        , IIdentityIsAuthenticated	
        , IIdentityAuthenticationType
    )
    values
    (
		@PublicationId
		, @AssemblyId
		, @ThreadId
		, @AppDomainId
		, @MachineId
		, @ProcessId
        , @LogEntry
        , @Priority
        , @Type
        , @SerializedIIdentityXml	
        , @IIdentityName			
        , @IIdentityIsAuthenticated	
        , @IIdentityAuthenticationType
    )
    
set @LogEntryId = scope_identity()
    
    commit transaction
       
end try

begin catch
    rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_LogEntryInsert] to [DiagnosticsRole]
go

-- create stored procedure 'usp_ExceptionInsert'
create procedure [dbo].[usp_ExceptionInsert]
    @ExceptionId					int						output
    , @PublicationId				uniqueidentifier
	, @TargetSiteId					int
	, @ThreadId                     int
	, @AssemblyId                   int
	, @AppDomainId                  int
	, @MachineId                    int
	, @ExceptionType				nvarchar(255)	
	, @HelpLink						nvarchar(512)
	, @HResult						int
	, @InnerExceptionId				int
	, @IsTransient					bit
	, @Message						nvarchar(512)
	, @Source						nvarchar(512)
	, @StackTrace					nvarchar(max)
	, @ProcessId					int
	, @SerializedExceptionXml		xml
	, @SerializedIIdentityXml		xml
	, @IIdentityName				nvarchar(255)
	, @IIdentityIsAuthenticated		bit
	, @IIdentityAuthenticationType	nvarchar(100)
as

set nocount on

begin try

    begin transaction
    
    insert into [dbo].[Exception]
    (
		PublicationId
        , TargetSiteId
        , ThreadId				
        , AssemblyId
        , AppDomainId
        , MachineId
        , ExceptionType			
        , HelpLink					
        , HResult					
        , InnerExceptionId			
        , IsTransient				
        , Message					
        , Source					
        , StackTrace				
        , ProcessId
        , SerializedExceptionXml	
        , SerializedIIdentityXml	
        , IIdentityName			
        , IIdentityIsAuthenticated	
        , IIdentityAuthenticationType
    )
    values
    (
		@PublicationId
        , @TargetSiteId
        , @ThreadId
        , @AssemblyId	
        , @AppDomainId	
        , @MachineId		
        , @ExceptionType			
        , @HelpLink					
        , @HResult					
        , @InnerExceptionId			
        , @IsTransient				
        , @Message					
        , @Source					
        , @StackTrace				
        , @ProcessId		
        , @SerializedExceptionXml	
        , @SerializedIIdentityXml	
        , @IIdentityName			
        , @IIdentityIsAuthenticated	
        , @IIdentityAuthenticationType
    )
    
    set @ExceptionId = scope_identity()
    
    commit transaction
       
end try

begin catch
    rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_ExceptionInsert] to [DiagnosticsRole]
go

-- create stored procedure 'usp_MachineInsert'
create procedure [dbo].[usp_MachineInsert]
    @MachineId  					int						output
	, @MachineName                  nvarchar(100)
	, @MachineIPAddress             nvarchar(100)
	, @OSVersion                    nvarchar(100)
	, @ProcessorCount               int
	, @InstalledMemory              bigint
	, @InstalledMemorySpeed         int
as

set nocount on

begin try

    begin transaction
    
    if not exists(select * from [dbo].[Machine] where MachineName = @MachineName and
                                                      MachineIPAddress = @MachineIPAddress and 
                                                      OSVersion = @OSVersion and
                                                      ProcessorCount = @ProcessorCount and
                                                      InstalledMemory = @InstalledMemory and
                                                      InstalledMemorySpeed = @InstalledMemorySpeed)

            begin
                
                insert into [dbo].[Machine]
                (
                    MachineName				
                    , MachineIPAddress
                    , OSVersion
                    , ProcessorCount
                    , InstalledMemory
                    , InstalledMemorySpeed
                )
                values
                (
                    @MachineName				
                    , @MachineIPAddress
                    , @OSVersion
                    , @ProcessorCount
                    , @InstalledMemory
                    , @InstalledMemorySpeed
                )
                
                set @MachineId = scope_identity()
             
            end
    else
            begin
            
                select @MachineId = MachineId from [dbo].[Machine] where MachineName = @MachineName and
                                                                         MachineIPAddress = @MachineIPAddress and 
                                                                         OSVersion = @OSVersion and
                                                                         ProcessorCount = @ProcessorCount and
                                                                         InstalledMemory = @InstalledMemory and
                                                                         InstalledMemorySpeed = @InstalledMemorySpeed 
            
            end
    
    commit transaction
       
end try

begin catch
    rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_MachineInsert] to [DiagnosticsRole]
go

-- create stored procedure 'usp_TargetSiteInsert'
create procedure usp_TargetSiteInsert
    @TargetSiteId					    int						output
    , @IsAbstract					    bit	
    , @IsAssembly					    bit	
    , @IsConstructor					bit	
    , @IsFamily						    bit	
    , @IsFamilyAndAssembly			    bit	
    , @IsFamilyOrAssembly			    bit	
    , @IsFinal						    bit	
    , @IsHideBySignature				bit	
    , @IsPrivate						bit	
    , @IsPublic						    bit	
    , @IsSpecialName					bit	
    , @IsStatic						    bit	
    , @IsVirtual						bit	
as

set nocount on

begin try

    begin transaction
    
    if not exists(select * from [dbo].[TargetSite] where IsAbstract = @IsAbstract and
                                                         IsAssembly = @IsAssembly and
                                                         IsConstructor = @IsConstructor and
                                                         IsFamily = @IsFamily and
                                                         IsFamilyAndAssembly = @IsFamilyAndAssembly and
                                                         IsFamilyOrAssembly = @IsFamilyOrAssembly and
                                                         IsFinal = @IsFinal and
                                                         IsHideBySignature = @IsHideBySignature and
                                                         IsPrivate = @IsPrivate and
                                                         IsPublic = @IsPublic and
                                                         IsSpecialName = @IsSpecialName and
                                                         IsStatic = @IsStatic and
                                                         IsVirtual = @IsVirtual)

            begin
    
                insert into [dbo].[TargetSite]
                (
                    IsAbstract
                    , IsAssembly
                    , IsConstructor
                    , IsFamily
                    , IsFamilyAndAssembly
                    , IsFamilyOrAssembly
                    , IsFinal
                    , IsHideBySignature
                    , IsPrivate
                    , IsPublic
                    , IsSpecialName
                    , IsStatic
                    , IsVirtual
                )
                values
                (
                    @IsAbstract
                    , @IsAssembly
                    , @IsConstructor
                    , @IsFamily
                    , @IsFamilyAndAssembly
                    , @IsFamilyOrAssembly
                    , @IsFinal
                    , @IsHideBySignature
                    , @IsPrivate
                    , @IsPublic
                    , @IsSpecialName
                    , @IsStatic
                    , @IsVirtual
                )
                
                set @TargetSiteId = scope_identity()
            
            end
            
        else
        
            begin
            
                select @TargetSiteId = TargetSiteId from [dbo].[TargetSite] where IsAbstract = @IsAbstract and
                                                                                  IsAssembly = @IsAssembly and
                                                                                  IsConstructor = @IsConstructor and
                                                                                  IsFamily = @IsFamily and
                                                                                  IsFamilyAndAssembly = @IsFamilyAndAssembly and
                                                                                  IsFamilyOrAssembly = @IsFamilyOrAssembly and
                                                                                  IsFinal = @IsFinal and
                                                                                  IsHideBySignature = @IsHideBySignature and
                                                                                  IsPrivate = @IsPrivate and
                                                                                  IsPublic = @IsPublic and
                                                                                  IsSpecialName = @IsSpecialName and
                                                                                  IsStatic = @IsStatic and
                                                                                  IsVirtual = @IsVirtual

            end
    
    commit transaction

end try

begin catch
    rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_TargetSiteInsert] to [DiagnosticsRole]
go

-- create stored procedure 'usp_AssemblyInsert'
create procedure usp_AssemblyInsert
    @AssemblyId					        int						output
    , @CodeBase                         nvarchar(1024)
    , @EntryPoint                       nvarchar(1024)
    , @FullName                         nvarchar(512)
    , @GlobalAssemblyCache              bit          
    , @HostContext                      bigint       
    , @ImageRuntimeVersion              nvarchar(100)
    , @Location                         nvarchar(512)
    , @ManifestModule                   nvarchar(1024)
    , @DebugBuild                       bit
as

set nocount on

begin try

    begin transaction
    
    if not exists(select * from [dbo].[Assembly] where CodeBase = @CodeBase and
                                                       (EntryPoint = @EntryPoint or (EntryPoint is null and @EntryPoint is null)) and
                                                       FullName = @FullName and
                                                       GlobalAssemblyCache = @GlobalAssemblyCache and
                                                       HostContext = @HostContext and
                                                       ImageRuntimeVersion = @ImageRuntimeVersion and
                                                       Location = @Location and
                                                       ManifestModule = @ManifestModule and
                                                       DebugBuild = @DebugBuild)

            begin
    
                insert into [dbo].[Assembly]
                (
                    CodeBase
                    , EntryPoint
                    , FullName
                    , GlobalAssemblyCache
                    , HostContext
                    , ImageRuntimeVersion
                    , Location
                    , ManifestModule
                    , DebugBuild
                )
                values
                (
                    @CodeBase
                    , @EntryPoint
                    , @FullName
                    , @GlobalAssemblyCache
                    , @HostContext
                    , @ImageRuntimeVersion
                    , @Location
                    , @ManifestModule
                    , @DebugBuild
                )
                
                set @AssemblyId = scope_identity()
    
            end
            
        else
        
            begin
            
                select @AssemblyId = AssemblyId from [dbo].[Assembly] where CodeBase = @CodeBase and
                                                                            (EntryPoint = @EntryPoint or (EntryPoint is null and @EntryPoint is null)) and
                                                                            FullName = @FullName and
                                                                            GlobalAssemblyCache = @GlobalAssemblyCache and
                                                                            HostContext = @HostContext and
                                                                            ImageRuntimeVersion = @ImageRuntimeVersion and
                                                                            Location = @Location and
                                                                            ManifestModule = @ManifestModule and
                                                                            DebugBuild = @DebugBuild

            end
    
    commit transaction

end try

begin catch
    rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_AssemblyInsert] to [DiagnosticsRole]
go

-- create stored procedure 'usp_ThreadInsert'
create procedure usp_ThreadInsert
    @ThreadId					        int						output
    , @IsAlive                          bit
    , @IsBackground                     bit
    , @IsThreadPoolThread               bit
    , @ManagedThreadId                  int
    , @Name                             nvarchar(512)
    , @Priority                         nvarchar(100)
    , @ThreadState                      nvarchar(100)
    , @CurrentCulture 					nvarchar(100)
    , @CurrentUICulture 				nvarchar(100)
as

set nocount on

begin try

    begin transaction
    
        if not exists(select * from [dbo].[Thread] where IsAlive = @IsAlive and
                                                         IsBackground = @IsBackground and
                                                         IsThreadPoolThread = @IsThreadPoolThread and
                                                         ManagedThreadId = @ManagedThreadId and
                                                         ([Name] = @Name or ([Name] is null and @Name is null)) and
                                                         Priority = @Priority and
                                                         ThreadState = @ThreadState)

            begin
    
                insert into [dbo].[Thread]
                (
                    IsAlive            
                    , IsBackground      
                    , IsThreadPoolThread
                    , ManagedThreadId  
                    , [Name]             
                    , Priority          
                    , ThreadState
                    , CurrentCulture
                    , CurrentUICulture
                )
                values
                (
                    @IsAlive            
                    , @IsBackground       
                    , @IsThreadPoolThread 
                    , @ManagedThreadId    
                    , @Name               
                    , @Priority           
                    , @ThreadState
                    , @CurrentCulture
                    , @CurrentUICulture
                )
                
                set @ThreadId = scope_identity()
                
            end
            
        else
        
            begin
            
                select @ThreadId = ThreadId  from [dbo].[Thread] where IsAlive = @IsAlive and
                                                                       IsBackground = @IsBackground and
                                                                       IsThreadPoolThread = @IsThreadPoolThread and
                                                                       ManagedThreadId = @ManagedThreadId and
                                                                       ([Name] = @Name or ([Name] is null and @Name is null)) and
                                                                       Priority = @Priority and
                                                                       ThreadState = @ThreadState

            end
    
    commit transaction

end try

begin catch
    rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_ThreadInsert] to [DiagnosticsRole]
go

-- create stored procedure 'usp_AppDomainInsert'
create procedure usp_AppDomainInsert
    @AppDomainId					    int						output
    , @BaseDirectory                    nvarchar(1024)
    , @DomainManager                    nvarchar(100)			= null
    , @FriendlyName                     nvarchar(1024)
    , @Id                               int
as

set nocount on

begin try

    begin transaction
    
        if not exists(select * from [dbo].[AppDomain] where BaseDirectory = @BaseDirectory and
                                                             (DomainManager = @DomainManager or (DomainManager is null and @DomainManager is null)) and
                                                             FriendlyName = @FriendlyName and
                                                             Id = @Id)

            begin
    
                insert into [dbo].[AppDomain]
                (
                    BaseDirectory            
                    , DomainManager      
                    , FriendlyName
                    , Id
                )
                values
                (
                    @BaseDirectory            
                    , @DomainManager       
                    , @FriendlyName 
                    , @Id
                )
                
                set @AppDomainId = scope_identity()
                
            end

        else
        
            begin
    
                select @AppDomainId = AppDomainId  from [dbo].[AppDomain] where BaseDirectory = @BaseDirectory and
                                                                          (DomainManager = @DomainManager or (DomainManager is null and @DomainManager is null)) and
                                                                          FriendlyName = @FriendlyName and
                                                                          Id = @Id

            end
                                                                      
    commit transaction

end try

begin catch
    rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_AppDomainInsert] to [DiagnosticsRole]
go

-- create stored procedure 'usp_ProcessorInsert'
create procedure usp_ProcessorInsert
    @ProcessorId                        int						output
    , @MachineId                        int
    , @DeviceId                         nvarchar(20)
    , @Architecture                     int
    , @Name                             nvarchar(100)
    , @Description                      nvarchar(100)
    , @Manufacturer 					nvarchar(100)
    , @Cores 							int
as

set nocount on

begin try

    begin transaction
    
    if not exists(select * from [dbo].[Processor] where DeviceId = @DeviceId and
                                                        Architecture = @Architecture and
                                                        [Name] = @Name and
                                                        Description = @Description and
                                                        Manufacturer = @Manufacturer and
                                                        Cores = @Cores)
                                                        
        begin
    
            insert into [dbo].[Processor]
            (
                DeviceId
                , Architecture
                , [Name]
                , Description
                , Manufacturer
                , Cores
            )
            values
            (
                @DeviceId
                , @Architecture
                , @Name
                , @Description
                , @Manufacturer
                , @Cores
            )
            
            set @ProcessorId = scope_identity()
        
        end
        
    else
    
        begin
        
            select @ProcessorId = ProcessorId  from [dbo].[Processor] where DeviceId = @DeviceId and
                                                                            Architecture = @Architecture and
                                                                            [Name] = @Name and
                                                                            Description = @Description and
                                                                            Manufacturer = @Manufacturer and
                                                                            Cores = @Cores
        
        end
    
    if not exists(select * from [dbo].[MachineProcessor] where MachineId = @MachineId and
                                                               ProcessorId = @ProcessorId)
                                                               
        begin
         
            insert into [dbo].[MachineProcessor]
            (
                MachineId
                , ProcessorId
            )
            values
            (
                @MachineId
                , @ProcessorId
            )
    
        end
    
    commit transaction

end try

begin catch
    rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_ProcessorInsert] to [DiagnosticsRole]
go

-- create stored procedure 'usp_GraphicsProcessorInsert'
create procedure usp_GraphicsProcessorInsert
    @GraphicsProcessorId                int						output
    , @MachineId                        int
    , @DeviceId                         nvarchar(20)
    , @Name                             nvarchar(100)
    , @Description                      nvarchar(100)
    , @InstalledMemory                  bigint
    , @DriverVersion                    nvarchar(100)
as

set nocount on

begin try

    begin transaction

	if not exists(select * from [dbo].[GraphicsProcessor] where DeviceId = @DeviceId and
                                                                [Name] = @Name and 
														        Description = @Description and
														        InstalledMemory = @InstalledMemory and
														        (DriverVersion = @DriverVersion or (DriverVersion is null and @DriverVersion is null)))

		begin
    
			insert into [dbo].[GraphicsProcessor]
			(
				DeviceId
				, [Name]
				, Description
				, InstalledMemory
				, DriverVersion
			)
			values
			(
				@DeviceId
				, @Name
				, @Description
				, @InstalledMemory
				, @DriverVersion
			)
		    
			set @GraphicsProcessorId = scope_identity()

		end

	else
    
		begin

			select @GraphicsProcessorId = GraphicsProcessorId from [dbo].[GraphicsProcessor] where DeviceId = @DeviceId and
                                                                                                   [Name] = @Name and 
														                                           Description = @Description and
														                                           InstalledMemory = @InstalledMemory and
														                                           (DriverVersion = @DriverVersion or (DriverVersion is null and @DriverVersion is null))
														                                   
		end

    if not exists(select * from [dbo].[MachineGraphicsProcessor] where MachineId = @MachineId and
                                                                       GraphicsProcessorId = @GraphicsProcessorId)
                                                                       
        begin
        
            insert into [dbo].[MachineGraphicsProcessor]
            (
                MachineId
                , GraphicsProcessorId
            )
            values
            (
                @MachineId
                , @GraphicsProcessorId
            )
            
        end
    
    commit transaction

end try

begin catch
    rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_GraphicsProcessorInsert] to [DiagnosticsRole]
go

-- create stored procedure 'usp_ProcessInsert'
create procedure usp_ProcessInsert
	@ProcessId							int						output
	, @BasePriority						int
	, @ExitCode 						int
	, @ExitTime 						datetime
	, @HasExited 						bit
	, @Id 								int
	, @NonpagedSystemMemorySize64 		bigint
	, @PagedMemorySize64 				bigint
	, @PagedSystemMemorySize64 			bigint
	, @PeakPagedMemorySize64 			bigint
	, @PeakVirtualMemorySize64 			bigint
	, @PeakWorkingSet64   				bigint
	, @PriorityBoostEnabled 			bit
	, @PrivateMemorySize64 				bigint
	, @PrivilegedProcessorTime 			nvarchar(40)
	, @ProcessName 						nvarchar(100)
	, @ProcessorAffinity 				int
	, @Responding 						bit
	, @SessionId 						int
	, @StartTime  						datetime
	, @Threads 							int
	, @TotalProcessorTime 				nvarchar(40)
	, @UserProcessorTime 				nvarchar(40)
	, @VirtualMemorySize64 				bigint
	, @WorkingSet64 					bigint
as

set nocount on

begin try

	begin transaction

    	if not exists(select * from [dbo].[Process] where BasePriority = @BasePriority and
                                                                (ExitCode = @ExitCode or (ExitCode is null and @ExitCode is null)) and
                                                                (ExitTime = @ExitTime or (ExitTime is null and @ExitTime is null)) and
                                                                HasExited = @HasExited and
                                                                Id = @Id and
                                                                NonpagedSystemMemorySize64 = @NonpagedSystemMemorySize64 and
                                                                PagedMemorySize64 = @PagedMemorySize64 and
                                                                PagedSystemMemorySize64 = @PagedSystemMemorySize64 and
                                                                PeakPagedMemorySize64 = @PeakPagedMemorySize64 and
                                                                PeakVirtualMemorySize64 = @PeakVirtualMemorySize64 and
                                                                PeakWorkingSet64 = @PeakWorkingSet64 and
                                                                PriorityBoostEnabled = @PriorityBoostEnabled and
                                                                PrivateMemorySize64 = @PrivateMemorySize64 and
                                                                PrivilegedProcessorTime = @PrivilegedProcessorTime and
                                                                ProcessName = @ProcessName and
                                                                ProcessorAffinity = @ProcessorAffinity and
                                                                Responding = @Responding and
                                                                SessionId = @SessionId and
                                                                StartTime = @StartTime and
                                                                [Threads] = @Threads and
                                                                TotalProcessorTime = @TotalProcessorTime and
                                                                UserProcessorTime = @UserProcessorTime and
                                                                VirtualMemorySize64 = @VirtualMemorySize64 and
                                                                WorkingSet64 = @WorkingSet64)
                                                                
		begin
    
			insert into [dbo].[Process]
			(
				BasePriority
				, ExitCode
				, ExitTime 					
				, HasExited
				, Id
				, NonpagedSystemMemorySize64
				, PagedMemorySize64
				, PagedSystemMemorySize64
				, PeakPagedMemorySize64
				, PeakVirtualMemorySize64
				, PeakWorkingSet64
				, PriorityBoostEnabled
				, PrivateMemorySize64
				, PrivilegedProcessorTime
				, ProcessName
				, ProcessorAffinity
				, Responding
				, SessionId
				, StartTime
				, [Threads]
				, TotalProcessorTime
				, UserProcessorTime
				, VirtualMemorySize64
				, WorkingSet64				
			)
			values
			(
				@BasePriority
				, @ExitCode
				, @ExitTime 					
				, @HasExited
				, @Id
				, @NonpagedSystemMemorySize64
				, @PagedMemorySize64
				, @PagedSystemMemorySize64
				, @PeakPagedMemorySize64
				, @PeakVirtualMemorySize64
				, @PeakWorkingSet64
				, @PriorityBoostEnabled
				, @PrivateMemorySize64
				, @PrivilegedProcessorTime
				, @ProcessName
				, @ProcessorAffinity
				, @Responding
				, @SessionId
				, @StartTime
				, @Threads
				, @TotalProcessorTime
				, @UserProcessorTime
				, @VirtualMemorySize64
				, @WorkingSet64
			)
		    
			set @ProcessId = scope_identity()

		end

	else
    
		begin

			select @ProcessId = ProcessId from [dbo].[Process] where BasePriority = @BasePriority and
                                                                (ExitCode = @ExitCode or (ExitCode is null and @ExitCode is null)) and 
                                                                (ExitTime = @ExitTime or (ExitTime is null and @ExitTime is null)) and
                                                                HasExited = @HasExited and
                                                                Id = @Id and
                                                                NonpagedSystemMemorySize64 = @NonpagedSystemMemorySize64 and
                                                                PagedMemorySize64 = @PagedMemorySize64 and
                                                                PagedSystemMemorySize64 = @PagedSystemMemorySize64 and
                                                                PeakPagedMemorySize64 = @PeakPagedMemorySize64 and
                                                                PeakVirtualMemorySize64 = @PeakVirtualMemorySize64 and
                                                                PeakWorkingSet64 = @PeakWorkingSet64 and
                                                                PriorityBoostEnabled = @PriorityBoostEnabled and
                                                                PrivateMemorySize64 = @PrivateMemorySize64 and
                                                                PrivilegedProcessorTime = @PrivilegedProcessorTime and
                                                                ProcessName = @ProcessName and
                                                                ProcessorAffinity = @ProcessorAffinity and
                                                                Responding = @Responding and
                                                                SessionId = @SessionId and
                                                                StartTime = @StartTime and
                                                                [Threads] = @Threads and
                                                                TotalProcessorTime = @TotalProcessorTime and
                                                                UserProcessorTime = @UserProcessorTime and
                                                                VirtualMemorySize64 = @VirtualMemorySize64 and
                                                                WorkingSet64 = @WorkingSet64														                                   
		end

    commit transaction

end try

begin catch
    rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_ProcessInsert] to [DiagnosticsRole]
go

-- create stored procedure 'usp_ExceptionSelect'
create procedure usp_ExceptionSelect
	@ExceptionId						int
	, @PublicationId					uniqueidentifier
as

set nocount on

begin try

	begin transaction

		select 
			ExceptionId
			, PublicationId
			, AssemblyId
			, TargetSiteId
			, ThreadId
			, AppDomainId
			, MachineId
			, ProcessId
			, ExceptionType
			, HelpLink
			, HResult
			, InnerExceptionId
			, IsTransient
			, Message
			, Source
			, StackTrace
			, SerializedExceptionXml
			, SerializedIIdentityXml
			, IIdentityName
			, IIdentityIsAuthenticated
			, IIdentityAuthenticationType
			, Created
		from
			[dbo].[Exception]
		where 
			(ExceptionId = @ExceptionId or (@ExceptionId is null)) and 
			(PublicationId = @PublicationId or (@PublicationId is null))

		commit transaction
			
end try

begin catch
	rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_ExceptionSelect] to [DiagnosticsRole]
go

-- create stored procedure 'usp_AppDomainSelect'
create procedure usp_AppDomainSelect
	@AppDomainId						int
as

set nocount on

begin try
	
	begin transaction
	
		select
			AppDomainId
			, BaseDirectory
			, DomainManager
			, FriendlyName
			, Id
			, Created
		from
			[dbo].[AppDomain]
		where
			(AppDomainId = @AppDomainId or (@AppDomainId is null))
	
	commit transaction
	
end try

begin catch
	rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_AppDomainSelect] to [DiagnosticsRole]
go

-- create stored procedure 'usp_AssemblySelect'
create procedure usp_AssemblySelect
	@AssemblyId 						int
as

set nocount on

begin try
	
	begin transaction
	
		select
			AssemblyId
			, CodeBase
			, EntryPoint
			, FullName
			, GlobalAssemblyCache
			, HostContext
			, ImageRuntimeVersion
			, Location
			, ManifestModule
			, DebugBuild
			, Created
		from
			[dbo].[Assembly]
		where
			(AssemblyId = @AssemblyId or (@AssemblyId is null))
	
	commit transaction
	
end try

begin catch
	rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_AssemblySelect] to [DiagnosticsRole]
go

-- create stored procedure 'usp_GraphicsProcessorSelect'
create procedure usp_GraphicsProcessorSelect
	@GraphicsProcessorId				int
	, @MachineId						int
as

set nocount on

begin try
	
	begin transaction
	
		select
			p.GraphicsProcessorId
			, DeviceId
			, [Name]
			, Description
			, InstalledMemory
			, DriverVersion
			, Created
		from
			[dbo].[GraphicsProcessor] as p inner join [dbo].[MachineGraphicsProcessor] as m on p.GraphicsProcessorId = m.GraphicsProcessorId 
		where
			(p.GraphicsProcessorId = @GraphicsProcessorId or (@GraphicsProcessorId is null)) and
			(m.MachineId = @MachineId or (@MachineId is null))
	
	commit transaction
	
end try

begin catch
	rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_GraphicsProcessorSelect] to [DiagnosticsRole]
go

-- create stored procedure 'usp_LogEntrySelect'
create procedure usp_LogEntrySelect
	@LogEntryId							int
as

set nocount on

begin try

	begin transaction

	select
		LogEntryId
		, AssemblyId
		, ThreadId
		, AppDomainId
		, MachineId
		, ProcessId
		, LogEntry
		, Priority
		, Type
		, SerializedIIdentityXml
		, IIdentityName
		, IIdentityIsAuthenticated
		, IIdentityAuthenticationType
		, Created
	from 
		[dbo].[LogEntry]
	where
		(LogEntryId = @LogEntryId or (@LogEntryId is null))

	commit transaction
	
end try

begin catch
	rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_LogEntrySelect] to [DiagnosticsRole]
go

-- create stored procedure 'usp_MachineSelect'
create procedure usp_MachineSelect
	@MachineId							int
as

set nocount on

begin try

	begin transaction

	select
		MachineId
		, MachineName
		, MachineIPAddress
		, OSVersion
		, ProcessorCount
		, InstalledMemory
		, InstalledMemorySpeed
		, Created
	from 
		[dbo].[Machine]
	where
		(MachineId = @MachineId or (@MachineId is null))

	commit transaction
	
end try

begin catch
	rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_MachineSelect] to [DiagnosticsRole]
go

-- create stored procedure 'usp_ProcessSelect'
create procedure usp_ProcessSelect
	@ProcessId							int
as

set nocount on

begin try

	begin transaction

	select
		ProcessId
		, BasePriority
		, ExitCode
		, ExitTime
		, HasExited
		, Id
		, NonpagedSystemMemorySize64
		, PagedMemorySize64
		, PagedSystemMemorySize64
		, PeakPagedMemorySize64
		, PeakVirtualMemorySize64
		, PeakWorkingSet64
		, PriorityBoostEnabled
		, PrivateMemorySize64
		, PrivilegedProcessorTime
		, ProcessName
		, ProcessorAffinity
		, Responding
		, SessionId
		, StartTime
		, [Threads]
		, TotalProcessorTime
		, UserProcessorTime
		, VirtualMemorySize64
		, WorkingSet64
		, Created
	from 
		[dbo].[Process]
	where
		(ProcessId = @ProcessId or (@ProcessId is null))

	commit transaction
	
end try

begin catch
	rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_ProcessSelect] to [DiagnosticsRole]
go

-- create stored procedure 'usp_ProcesorSelect'
create procedure usp_ProcessorSelect
	@ProcessorId						int
	, @MachineId						int
as

set nocount on

begin try

	begin transaction

	select
		p.ProcessorId
		, DeviceId
		, Architecture
		, [Name]
		, Description
		, Manufacturer
		, Cores
		, Created
	from 
		[dbo].[Processor] as p inner join [dbo].[MachineProcessor] as m on p.ProcessorId = m.ProcessorId 
	where
		(p.ProcessorId = @ProcessorId or (@ProcessorId is null)) and
		(m.MachineId = @MachineId or (@MachineId is null))

	commit transaction
	
end try

begin catch
	rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_ProcessorSelect] to [DiagnosticsRole]
go


-- create stored procedure 'usp_TargetSiteSelect'
create procedure usp_TargetSiteSelect
	@TargetSiteId						int
	, @ExceptionId						int
as

set nocount on

begin try

	begin transaction
	
	select 
		t.TargetSiteId
		, IsAbstract
		, IsAssembly
		, IsConstructor
		, IsFamily
		, IsFamilyAndAssembly
		, IsFamilyOrAssembly
		, IsFinal
		, IsHideBySignature
		, IsPrivate
		, IsPublic
		, IsSpecialName
		, IsStatic
		, IsVirtual
		, t.Created
	from
		[dbo].[TargetSite] as t inner join [dbo].[Exception] as e on t.TargetSiteId = e.TargetSiteId
	where
		(t.TargetSiteId = @TargetSiteId or (@TargetSiteId is null)) and
		(ExceptionId = @ExceptionId or (@ExceptionId is null))		
		
	commit transaction
end try

begin catch
	rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_TargetSiteSelect] to [DiagnosticsRole]
go

-- create stored procedure 'usp_ThreadSelect'
create procedure usp_ThreadSelect
	@ThreadId							int
	, @ExceptionId						int
as

set nocount on

begin try

	begin transaction
	
	select
		t.ThreadId
		, IsAlive
		, IsBackground
		, IsThreadPoolThread
		, ManagedThreadId
		, [Name]
		, Priority
		, ThreadState
		, CurrentCulture
		, CurrentUICulture
		, t.Created
	from 
		[dbo].[Thread] as t inner join [dbo].[Exception] as e on t.ThreadId = e.ThreadId
	where
		(t.ThreadId = @ThreadId or (@ThreadId is null)) and
		(ExceptionId = @ExceptionId or (@ExceptionId is null))
	
	commit 

end try

begin catch
	rollback transaction
end catch

set nocount off

go

-- grant execute permissions
grant execute on [dbo].[usp_ThreadSelect] to [DiagnosticsRole]
go
